# -*- coding: UTF-8 -*-
"""
@auth:buxiangjie
@date:2021/11/5 13:22
@describe: 
"""

from db_operate.conn_db import ConnMysql
from common.excep import excep
from log.ulog import Ulog
from common.common import Encoder

import json

import allure

log = Ulog().logger_()


class Saas(ConnMysql):

	@excep
	@allure.step("获取客群枚举")
	def get_enum(self, r: str = None, i: str = None):
		"""获取客群枚举"""
		relation_ship = {
			"法人代表": 1,
			"工商登记的股东": 2,
			"医疗机构执业许可证负责人": 3,
			"执业到诊所的医生": 4,
			"其他授权人": 5
		}
		is_doctor = {
			"是": 1,
			"助理医生": 2,
			"否": 0
		}
		result = {}
		if r is None and i is None:
			raise ValueError
		if r is not None:
			result["relation_ship"] = relation_ship[r]
		if i is not None:
			result["is_doctor"] = is_doctor[i]
		return result

	@excep
	@allure.step("获取产品配置信息")
	def get_product(self, env: str, **kwargs):
		"""获取product表的channel"""
		sql = f"""
			select channel,product_name,vendor_id,group_id,group_name,channel 
			from product
			where product_code='{kwargs["product"]}';
			"""
		res = json.loads(json.dumps(self.exec_select(sql, env, "saas")[0], cls=Encoder))
		return res

	@excep
	@allure.step("获取个人信息")
	def get_id_phone(self, env: str, **kwargs):
		"""获取个人信息"""
		if (kwargs["product"] is None) or (kwargs["product"] == ""):
			sql = f"""
			select card_num_encrypt as id,phone_encrypt as phone from project_customer_detail
			where length(card_num_encrypt)>0
			and length(phone_encrypt)>0
			order by create_time desc limit 1;
			"""
		elif kwargs["product"] is not None:
			sql = f"""
			select card_num_encrypt as id,phone_encrypt as phone from project_customer_detail
			where length(card_num_encrypt)>0
			and project_id in(
			select id from project_detail where 
			product_code in {str(kwargs["product"].split(",")).replace("[", "(").replace("]", ")")}
			)order by create_time desc limit 1;
			"""
		log.info(Saas.get_id_phone.__doc__)
		res = self.exec_select(sql, env, "saas")
		if res is None:
			return None
		else:
			return res[0]

	@excep
	@allure.step("获取车架号")
	def get_id_vin(self, env: str, **kwargs):
		"""获取车架号"""
		sql = f"""
		select pcd.card_num_encrypt as id,ped.vin as vin from project_customer_detail pcd, project_extra_detail ped
		where pcd.project_id=ped.project_id
		and length(pcd.card_num_encrypt)>0
		and ped.vin is not null order by ped.create_time desc limit 1;
		"""
		log.info(Saas.get_id_vin.__doc__)
		res = self.exec_select(sql, env, "saas")
		if res is None:
			return None
		else:
			return res[0]

	@excep
	@allure.step("获取诊所编号")
	def get_enterprise_certificate_num(self, env: str, **kwargs):
		"""获取诊所编号(统一社会信用代码)"""
		if kwargs["busi_type"] == "credit":
			sql = f"""
			select json_extract(ce.extra_info, '$.enterpriseCertificateNum')as enterprise 
			from credit_entity ce, credit c 
			where c.id=ce.credit_id
			and c.product_group_id=1099
			and CASE WHEN JSON_VALID(ce.extra_info) THEN 
			json_extract(ce.extra_info, '$.enterpriseCertificateNum') is not null
			ELSE null END
			order by ce.create_time desc limit 1;
			"""
		elif kwargs["busi_type"] == "project":
			sql = f"""
				select json_extract(ce.extra_info, '$.enterpriseCertificateNum')as enterprise  
				from project_entity_detail ce, project_detail c 
				where c.id=ce.project_id
				and c.product_code in ('XJ_JFX_YYDSIN','XJ_JFX_YYDMUL','XJ_JFX_YYDLAG')
				and CASE WHEN JSON_VALID(ce.extra_info) THEN 
				json_extract(ce.extra_info, '$.enterpriseCertificateNum') is not null
				ELSE null END
				order by ce.create_time desc limit 1;
				"""
		log.info(Saas.get_enterprise_certificate_num.__doc__)
		res = self.exec_select(sql, env, "saas")
		if res is None:
			return None
		else:
			return res[0]

	@excep
	@allure.step("获取法人信息")
	def get_legal_info(self, env: str, **kwargs):
		"""获取法人身份证号,手机号"""
		if kwargs["busi_type"] == "credit":
			sql = f"""
			select
			 ce.legal_person_idcard_encrypt as legal_id,
			 ce.legal_person_phone_encrypt as legal_phone,
			 ce.unified_social_credit_code as work_company_id
			from
			 credit_entity ce
			left join
			 credit c
			on
			 ce.credit_id = c.id
			where
			 c.product_code in {str(kwargs["product"].split(",")).replace("[", "(").replace("]", ")")}
			 and LENGTH(ce.legal_person_idcard_encrypt) > 0
			 or c.product_group_id = {self.get_product(env, **kwargs)["group_id"]}
			order by
			 ce.create_time desc
			limit 1;
			"""
		elif kwargs["busi_type"] == "project" or kwargs["busi_type"] == "":
			sql = f"""
					select
					 ce.legal_person_idcard_encrypt as legal_id,
					 ce.legal_person_phone_encrypt as legal_phone,
					 ce.unified_social_credit_code as work_company_id
					from
					 project_entity_detail ce
					left join
					 project_detail c
					on
					 ce.project_id = c.id
					where
					 c.product_code in {str(kwargs["product"].split(",")).replace("[", "(").replace("]", ")")}
					 and LENGTH(ce.legal_person_idcard_encrypt) > 0
					order by
					 ce.create_time desc
					limit 1;
					"""
		log.info(Saas.get_legal_info.__doc__)
		res = self.exec_select(sql, env, "saas")
		if res is None:
			return None
		else:
			return res[0]

	@excep
	@allure.step("获取场景方编号")
	def get_scene_holder_id(self, env: str, **kwargs):
		"""获取场景方统一社会信用代码"""
		if kwargs["product"] == "FQ_JFX_MYFQ":
			sql = f"""
					select ped.unified_social_credit_code as scene_id
					from project_entity_detail ped, project_detail p where
					p.id=ped.project_id
					and p.product_code = '{kwargs["product"]}'
					order by ped.create_time desc limit 1;;
					"""
		else:
			sql = f"""
			select json_extract(extra_info, '$.supplierUnifiedSocialCreditCode')as scene_id
			from project_entity_detail where
			CASE WHEN JSON_VALID(extra_info) THEN 
			json_extract(extra_info, '$.supplierUnifiedSocialCreditCode') is not null
			ELSE null END order by create_time desc limit 1;
			"""
		log.info(Saas.get_scene_holder_id.__doc__)
		res = self.exec_select(sql, env, "saas")
		if res is None:
			return None
		else:
			return res[0]

	@excep
	@allure.step("按身份证号查询，在小贷业务中当前逾期订单笔数")
	def idcard_asset_overdue_count_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中当前逾期订单笔数"""
		sql = f"""
		select ifnull(COUNT(*), 0)as c from asset a, project_customer_detail p
		where a.cur_overdue_days > 0 
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_overdue_count_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按手机号查询，在小贷业务中当前逾期订单笔数")
	def mobile_asset_overdue_count_cur_cloudloan(self, env: str, **kwargs):
		"""按手机号查询，在小贷业务中当前逾期订单笔数"""
		sql = f"""
		select ifnull(count(*), 0)as c from asset a, project_customer_detail p
		where a.cur_overdue_days > 0 
		AND p.phone_encrypt = '{kwargs["phone"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.mobile_asset_overdue_count_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中当前有效最大授信额度")
	def idcard_credit_amount_max_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中当前有效最大授信额度"""
		sql = f"""
		select a.total_amount from amount a, user_info u
		where u.id_card_encrypt = '{kwargs["id"]}' 
		and u.id = a.user_id 
		and a.expire = 0 
		order by a.total_amount desc limit 1;
		"""
		log.info(Saas.idcard_credit_amount_max_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("total_amount", None)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中当前有效授信额度总和")
	def idcard_credit_amount_sum_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中当前有效授信额度总和"""
		sql = f"""
		select ifnull(sum(a.total_amount), 0)as c from amount a, user_info u
		where u.id_card_encrypt = '{kwargs["id"]}'
		and u.id = a.user_id 
		and a.expire = 0;
		"""
		log.info(Saas.idcard_credit_amount_sum_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中当前在贷本金")
	def idcard_asset_inloan_principal_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where p.card_num_encrypt = '{kwargs["id"]}' 
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中当前在贷本金（除新罗马车贷）")
	def idcard_asset_inloan_principal_cur_exclude_xj_roma_carv2_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中当前在贷本金（除新罗马车贷）"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id 
		and a.product_code!="XJ_ROMA_CARV2";
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_exclude_xj_roma_carv2_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号和车架号查询，近30天内在小贷业务中已结清订单笔数")
	def idcard_vin_asset_clear_count_last30days_cloudloan(self, env: str, **kwargs):
		"""按身份证号和车架号查询，近30天内在小贷业务中已结清订单笔数"""
		sql = f"""
		select ifnull(count(1), 0)as c from asset where 
		asset_status=2
		and project_id in(
		select ped.project_id,pcd.project_id from project_extra_detail as ped 
		left join project_customer_detail as pcd 
		on ped.project_id=pcd.project_id
		where ped.vin='{kwargs["vin"]}'
		and pcd.card_num_encrypt='{kwargs["id"]}'
		)
		and datediff(curdate(), str_to_date(clear_time, '%Y-%m-%d'))<=30;
		"""
		log.info(Saas.idcard_vin_asset_clear_count_last30days_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号和车架号查询，近45天内在小贷业务中已结清订单笔数")
	def idcard_vin_asset_clear_count_last45days_cloudloan(self, env: str, **kwargs):
		"""按身份证号和车架号查询，近45天内在小贷业务中已结清订单笔数"""
		sql = f"""
			select ifnull(count(1), 0)as c from asset where 
			asset_status=2
			and project_id in(
			select ped.project_id,pcd.project_id from project_extra_detail as ped 
			left join project_customer_detail as pcd 
			on ped.project_id=pcd.project_id
			where ped.vin='{kwargs["vin"]}'
			and pcd.card_num_encrypt='{kwargs["id"]}'
			)
			and datediff(curdate(), str_to_date(clear_time, '%Y-%m-%d'))<=45;
			"""
		log.info(Saas.idcard_vin_asset_clear_count_last45days_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号和车架号查询，近45天内在小贷业务中已结清订单笔数")
	def idcard_vin_asset_clear_count_last60days_cloudloan(self, env: str, **kwargs):
		"""按身份证号和车架号查询，近45天内在小贷业务中已结清订单笔数"""
		sql = f"""
				select ifnull(count(1), 0)as c from asset where 
				asset_status=2
				and project_id in(
				select ped.project_id,pcd.project_id from project_extra_detail as ped 
				left join project_customer_detail as pcd 
				on ped.project_id=pcd.project_id
				where ped.vin='{kwargs["vin"]}'
				and pcd.card_num_encrypt='{kwargs["id"]}'
				)
				and datediff(curdate(), str_to_date(clear_time, '%Y-%m-%d'))<=60;
				"""
		log.info(Saas.idcard_vin_asset_clear_count_last60days_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按车架号查询，近30天内在小贷业务中已结清订单笔数")
	def vin_asset_clear_count_last30days_cloudloan(self, env: str, **kwargs):
		"""按车架号查询，近30天内在小贷业务中已结清订单笔数"""
		sql = f"""
		select ifnull(count(1), 0)as c from asset where 
		asset_status=2
		and project_id in(
		select project_id from project_extra_detail where
		vin='{kwargs["vin"]}'
		)
		and datediff(curdate(), str_to_date(clear_time, '%Y-%m-%d'))<=30;
		"""
		log.info(Saas.vin_asset_clear_count_last30days_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按车架号查询，近45天内在小贷业务中已结清订单笔数")
	def vin_asset_clear_count_last45days_cloudloan(self, env: str, **kwargs):
		"""按车架号查询，近45天内在小贷业务中已结清订单笔数"""
		sql = f"""
			select ifnull(count(1), 0)as c from asset where 
			asset_status=2
			and project_id in(
			select project_id from project_extra_detail where
			vin='{kwargs["vin"]}'
			)
			and datediff(curdate(), str_to_date(clear_time, '%Y-%m-%d'))<=45;
			"""
		log.info(Saas.vin_asset_clear_count_last45days_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按车架号查询，近60天内在小贷业务中已结清订单笔数")
	def vin_asset_clear_count_last60days_cloudloan(self, env: str, **kwargs):
		"""按车架号查询，近60天内在小贷业务中已结清订单笔数"""
		sql = f"""
			select ifnull(count(1), 0)as c from asset where 
			asset_status=2
			and project_id in(
			select project_id from project_extra_detail where
			vin='{kwargs["vin"]}'
			)
			and datediff(curdate(), str_to_date(clear_time, '%Y-%m-%d'))<=60;
			"""
		log.info(Saas.vin_asset_clear_count_last60days_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务其他产品（即不包含产品卡卡贷）中当前在贷本金")
	def idcard_asset_inloan_principal_cur_exclude_xj_wx_kkd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务其他产品（即不包含产品卡卡贷）中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code !="XJ_WX_KKD"
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_exclude_xj_wx_kkd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务其他产品（即不包含产品豆豆钱）中当前在贷本金")
	def idcard_asset_inloan_principal_cur_exclude_xj_wx_ddq_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务其他产品（即不包含产品豆豆钱）中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code !="XJ_WX_DDQ"
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_exclude_xj_wx_ddq_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务卡卡贷产品中当前在贷本金")
	def idcard_asset_inloan_principal_cur_xj_wx_kkd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务卡卡贷产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code="XJ_WX_KKD"
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_xj_wx_kkd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务豆豆钱产品中当前在贷本金")
	def idcard_asset_inloan_principal_cur_xj_wx_ddq_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务豆豆钱产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code ="XJ_WX_DDQ"
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_xj_wx_ddq_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务其他产品（即不包含产品即科医疗）中当前在贷本金")
	def idcard_asset_inloan_principal_cur_exclude_fq_jk_jfqyl_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务其他产品（即不包含产品即科医疗）中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code not in("FQ_JK_JFQYL","FQ_JK_JFQYLV2")
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_exclude_fq_jk_jfqyl_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务其他产品（即不包含产品即科医疗）中当前在贷本金")
	def idcard_asset_inloan_principal_cur_exclude_fq_jk_jfqjy_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务其他产品（即不包含产品即科教育）中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code not in("FQ_JK_JFQJY","FQ_JK_JFQJYV2")
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_exclude_fq_jk_jfqjy_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务即科教育产品中当前在贷本金")
	def idcard_asset_inloan_principal_fq_jk_jfqjy_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务即科教育产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code in("FQ_JK_JFQJY","FQ_JK_JFQJYV2")
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_fq_jk_jfqjy_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务即科医疗产品中当前在贷本金")
	def idcard_asset_inloan_principal_fq_jk_jfqyl_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务即科医疗产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code in("FQ_JK_JFQYL","FQ_JK_JFQYLV2")
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_fq_jk_jfqyl_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证查询，在小贷业务的历史最大逾期天数")
	def idcard_asset_max_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按身份证查询，在小贷业务的历史最大逾期天数"""
		sql = f"""
		select ifnull(Max(a.largest_overdue_days), 0)as c from asset a, project_customer_detail p
		where a.largest_overdue_days > 0 
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_max_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证查询，在小贷业务中历史逾期订单笔数")
	def idcard_asset_count_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按身份证查询，在小贷业务中历史逾期订单笔数"""
		sql = f"""
		select ifnull(count(1), 0)as c from asset a, project_customer_detail p
		where a.largest_overdue_days > 0 
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id and a.largest_overdue_days>0;
		"""
		log.info(Saas.idcard_asset_count_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按手机号查询，在小贷业务的历史最大逾期天数")
	def mobile_asset_max_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按手机号查询，在小贷业务的历史最大逾期天数"""
		sql = f"""
		select ifnull(max(a.largest_overdue_days), 0)as c from asset a, project_customer_detail p
		where a.largest_overdue_days > 0 
		AND p.phone_encrypt = '{kwargs["phone"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.mobile_asset_max_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按手机号查询，在小贷业务中历史逾期订单笔数")
	def mobile_asset_count_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按手机号查询，在小贷业务中历史逾期订单笔数"""
		sql = f"""
		select ifnull(count(*), 0)as c from asset a, project_customer_detail p
		where a.largest_overdue_days > 0 
		AND p.phone_encrypt = '{kwargs["phone"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.mobile_asset_count_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按渠道或产品查询，在小贷业务中的在贷总额")
	def channel_or_product_inloan_sum_cloudloan(self, env: str, **kwargs):
		"""按渠道或产品查询，在小贷业务中的在贷总额"""
		if kwargs["source_code"] != 0:
			sql = f"""
					select ifnull(sum(debt_amount), 0)as c from ds_fund_asset 
					where source_code={kwargs["source_code"]};
					"""
		if kwargs["product_code"]:
			sql = f"""
			select ifnull(sum(debt_amount), 0)as c from ds_fund_asset 
			where product_code in {str(kwargs["product_code"].split(",")).replace("[", "(").replace("]", ")")};
			"""
		log.info(Saas.channel_or_product_inloan_sum_cloudloan.__doc__)
		res = self.exec_select(sql, env, "data_station")[0].get("c", 0)
		return res

	@excep
	@allure.step("按渠道或产品查询，在小贷业务中的放款总额")
	def channel_or_product_loan_sum_cloudloan(self, env: str, **kwargs):
		"""按渠道或产品查询，在小贷业务中的放款总额"""
		sql = f"""
		select sum(amount)as c from ds_fund_asset 
		where source_code={kwargs["source_code"]} 
		or product_code in {str(kwargs["product_code"].split(",")).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.channel_or_product_loan_sum_cloudloan.__doc__)
		res = self.exec_select(sql, env, "data_station")[0].get("c", 0)
		return res

	@excep
	@allure.step("按自定义客群查询，在小贷业务中的在贷总金额")
	def custom_user_group_inloan_sum_cloudloan(self, env: str, **kwargs):
		"""按自定义客群查询，在小贷业务中的在贷总金额"""
		if kwargs["user_group"]:
			if kwargs["channel"] == "jfx":
				sql1 = f"""
				select project_id from project_customer_detail
				where
				Case When JSON_VALID(extra_info) Then 
				json_extract(extra_info, '$.isDoctor') = {kwargs["is_doctor"]}
				and json_extract(
				extra_info, '$.applicantClinicRelationship') = {kwargs["relation_ship"]}
				else null END;
				"""
				projects = self.exec_select(sql1, env, "saas")
				if projects is None:
					return 0
				project_list = []
				for p in projects:
					project_list.append(p["project_id"])
				sql = f"""
				select ifnull(sum(debt_amount), 0)as c from ds_fund_asset where
				project_id in {str(project_list).replace("[", "(").replace("]", ")")};
				"""
			elif kwargs["channel"] == "jkjr":
				sql = f"""
				select ifnull(sum(debt_amount), 0)as c from ds_fund_asset where
				product_code in {str(kwargs["product_code"].split(",")).replace("[", "(").replace("]", ")")}
				and maturity>{kwargs["term"]};
				"""
			elif kwargs["channel"] == "zqys":
				sql = f"""
							SELECT
							a.id 
						FROM
							asset a
							LEFT JOIN project_entity_detail ped ON a.project_id = ped.project_id 
						WHERE
							a.product_code = 'FQ_ZQYC_DCD' 
							AND a.asset_status = 1 
						AND
						CASE
							WHEN JSON_VALID( ped.extra_info ) THEN
							json_extract( ped.extra_info, '$.vehicleEnergyType' ) = {kwargs["vehicle_energy_type"]}
							ELSE NULL END;
						"""
				log.info(Saas.custom_user_group_inloan_sum_cloudloan.__doc__)
				asset_id = self.exec_select(sql, env)
				if asset_id is None:
					return 0
				asset_id_list = []
				for p in asset_id:
					asset_id_list.append(p["id"])
				sql = f"""
								select ifnull(sum(debt_amount), 0)as c from ds_fund_asset
								where 
								id in {str(asset_id_list).replace("[", "(").replace("]", ")")};
							"""
		else:
			sql = f"""
			select ifnull(sum(debt_amount), 0)as c from ds_fund_asset where 
			product_code in {str(kwargs["product_code"].split(",")).replace("[", "(").replace("]", ")")};
			"""
		log.info(Saas.custom_user_group_inloan_sum_cloudloan.__doc__)
		res = self.exec_select(sql, env, "data_station")[0].get("c", 0)
		return res

	@excep
	@allure.step("按自定义客群查询，在小贷业务中的冻结总金额")
	def custom_user_group_freeze_sum_cloudloan(self, env: str, **kwargs):
		"""按自定义客群查询，在小贷业务中的冻结总金额"""
		if kwargs["user_group"]:
			if kwargs["channel"] == "jfx":
				sql = f"""
				select ifnull(sum(apply_amount), 0)as c from project_detail where id in(
				select project_id from project_customer_detail
				where
				Case When JSON_VALID(extra_info) Then 
				json_extract(extra_info, '$.isDoctor') = {kwargs["is_doctor"]} 
				and json_extract(
				extra_info, '$.applicantClinicRelationship') = {kwargs["relation_ship"]}
				else null END) 
				and audit_result=1
				and loan_result!=1 and loan_step!=4;
				"""
			elif kwargs["channel"] == "jkjr":
				sql = f"""
							select ifnull(sum(apply_amount), 0)as c from project_detail where
							audit_result=1 and loan_result!=1 and loan_step!=4
							and product_code in
							{str(kwargs["product_code"].split(",")).replace("[", "(").replace("]", ")")}
							and loan_term>{kwargs["term"]};
							"""
			elif kwargs["channel"] == "zqys":
				sql = f"""
						SELECT
						ifnull(sum(p.apply_amount), 0) as c 
					FROM
						project_detail p
						LEFT JOIN project_entity_detail ped ON p.id = ped.project_id 
					WHERE
						p.product_code = 'FQ_ZQYC_DCD' 
						AND p.audit_result = 1 
						AND p.loan_result != 1
						AND p.loan_step != 4
					AND
					CASE
						WHEN JSON_VALID( ped.extra_info ) THEN
						json_extract( ped.extra_info, '$.vehicleEnergyType' ) = {kwargs["vehicle_energy_type"]} 
						ELSE NULL END;
					"""
		else:
			sql = f"""
			select ifnull(sum(apply_amount), 0)as c from project_detail where
			audit_result=1 and loan_result!=1 and loan_step!=4
			and product_code in {str(kwargs["product_code"].split(",")).replace("[", "(").replace("]", ")")};
			"""
		log.info(Saas.custom_user_group_freeze_sum_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按诊所编号查询，在牙医贷业务中该诊所关联人员有效的授信额度总数")
	def enterpriseCertificateNum_effective_creditlimit_count_cur_yyd_cloudloan(self, env: str, **kwargs):
		"""按诊所编号查询，在牙医贷业务中该诊所关联人员有效的授信额度总数"""
		sql = f"""
		SELECT c.user_id AS user_id
		FROM credit c, credit_entity ce
		WHERE c.id = ce.credit_id
			AND ce.unified_social_credit_code = {kwargs["enterprise"]}
		UNION
		SELECT c.user_id
		FROM credit c, credit_entity ce
		WHERE c.id = ce.credit_id
			AND CASE 
				WHEN JSON_VALID(ce.extra_info) 
				THEN json_extract(ce.extra_info, '$.enterpriseCertificateNum') = {kwargs["enterprise"]}
				ELSE NULL
			END;
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql2 = f"""
						SELECT ifnull(count(user_id), 0) AS c
						FROM amount
						WHERE product_group_id = 1099
							AND expire = 0
							AND user_id IN {str(user_list).replace("[", "(").replace("]", ")")}
						"""
		log.info(Saas.enterpriseCertificateNum_effective_creditlimit_count_cur_yyd_cloudloan.__doc__)
		res = self.exec_select(sql2, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务的当前最大逾期天数")
	def idcard_asset_max_overdue_days_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务的当前最大逾期天数"""
		sql = f"""
		select ifnull(max(a.cur_overdue_days), 0) as c
		from asset a, project_customer_detail p
		where a.project_id = p.project_id 
		AND p.card_num_encrypt = '{kwargs["id"]}';
		"""
		log.info(Saas.idcard_asset_max_overdue_days_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务的当前逾期在贷本金")
	def idcard_asset_overdue_inloan_principal_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务的当前逾期在贷本金"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset a, project_customer_detail p
		where a.project_id = p.project_id 
		and a.cur_overdue_days > 0
		AND p.card_num_encrypt = '{kwargs["id"]}';
		"""
		log.info(Saas.idcard_asset_overdue_inloan_principal_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按手机号查询，在小贷业务的当前最大逾期天数")
	def mobile_asset_max_overdue_days_cur_cloudloan(self, env: str, **kwargs):
		"""按手机号查询，在小贷业务的当前最大逾期天数"""
		sql = f"""
		select ifnull(max(a.cur_overdue_days), 0)as c from asset a, project_customer_detail p
		where a.project_id = p.project_id 
		and a.cur_overdue_days > 0
		AND p.phone_encrypt = '{kwargs["phone"]}';
		"""
		log.info(Saas.mobile_asset_max_overdue_days_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务牙医贷产品中有效授信额度")
	def idcard_credit_amount_yyd_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务牙医贷产品中有效授信额度"""
		sql = f"""
		select ifnull(sum(a.total_amount), 0)as c from amount a, user_info u
		where u.id_card_encrypt = '{kwargs["id"]}'
		and u.id = a.user_id 
		and a.expire = 0 and a.product_group_id = 1099;
		"""
		log.info(Saas.idcard_credit_amount_yyd_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务牙医贷产品中当前在贷本金")
	def idcard_asset_inloan_principal_yyd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务牙医贷产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset
		where project_id in (
		select pd.id from project_detail pd, project_customer_detail pcd 
		where pcd.card_num_encrypt = '{kwargs["id"]}'
		and pd.product_code in ('XJ_JFX_YYDSIN','XJ_JFX_YYDMUL')
		and pd.id = pcd.project_id);
		"""
		log.info(Saas.idcard_asset_inloan_principal_yyd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务齿科商户贷产品中当前在贷本金")
	def idcard_asset_inloan_principal_ckshd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务齿科商户贷产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset a, project_customer_detail p
		where a.product_code ='FQ_JK_CKSHD'
		AND p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.idcard_asset_inloan_principal_ckshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务牙医贷产品中有效授信额度")
	def legal_person_id_credit_amount_yyd_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务牙医贷产品中有效授信额度"""
		sql = f"""
		select c.user_id from credit c, credit_entity ce
		where c.id = ce.credit_id 
		and ce.legal_person_idcard_encrypt = '{kwargs["legal_id"]}'
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
		select ifnull(sum(total_amount), 0)as c from amount 
		where product_group_id = '1099' 
		and expire = 0 and user_id in {str(user_list).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.legal_person_id_credit_amount_yyd_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务牙医贷产品中当前在贷本金")
	def legal_person_id_asset_inloan_principal_yyd_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务牙医贷产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset where project_id in (
		select p.id from project_detail p, project_entity_detail pe
		where p.id = pe.project_id 
		and p.product_code in ('XJ_JFX_YYDSIN', 'XJ_JFX_YYDMUL', 'XJ_JFX_YYDLAG')
		AND pe.legal_person_idcard_encrypt = '{kwargs["legal_id"]}');
		"""
		log.info(Saas.legal_person_id_asset_inloan_principal_yyd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按申请诊所统一社会信用代码查询，在小贷业务牙医贷产品中有效授信额度")
	def work_company_id_credit_amount_yyd_cur_cloudloan(self, env: str, **kwargs):
		"""按申请诊所统一社会信用代码查询，在小贷业务牙医贷产品中有效授信额度"""
		sql = f"""
		SELECT c.user_id as user_id
		FROM credit c, credit_entity ce
		WHERE c.id = ce.credit_id
			AND ce.unified_social_credit_code = '{kwargs["work_company_id"]}'
		UNION
		SELECT c.user_id
		FROM credit c, credit_entity ce
		WHERE c.id = ce.credit_id
			AND CASE 
				WHEN JSON_VALID(ce.extra_info) 
				THEN json_extract(ce.extra_info, '$.enterpriseCertificateNum') = '{kwargs["work_company_id"]}'
				ELSE NULL
			END
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
				SELECT ifnull(sum(total_amount), 0) AS c
				FROM amount
				WHERE product_group_id = 1099
					AND expire = 0
					AND user_id IN {str(user_list).replace("[", "(").replace("]", ")")};
				"""
		log.info(Saas.work_company_id_credit_amount_yyd_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按申请诊所统一社会信用代码查询，在小贷业务齿科商户贷产品中当前在贷本金")
	def work_company_id_asset_inloan_principal_ckshd_cloudloan(self, env: str, **kwargs):
		"""按申请诊所统一社会信用代码查询，在小贷业务齿科商户贷产品中当前在贷本金"""
		if kwargs["enterprise"] is not None:
			code = kwargs["enterprise"]
		else:
			code = kwargs["work_company_id"]
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id 
		and a.product_code = 'FQ_JK_CKSHD'
		AND p.unified_social_credit_code = '{code}';
		"""
		log.info(Saas.work_company_id_asset_inloan_principal_ckshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务中当前逾期订单笔数")
	def legal_person_idcard_asset_overdue_count_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务中当前逾期订单笔数"""
		sql = f"""
		select ifnull(COUNT(*), 0)as c from asset a, project_entity_detail p
		where a.cur_overdue_days > 0 
		AND p.legal_person_idcard_encrypt = '{kwargs["legal_id"]}'
		and a.project_id = p.project_id;
		"""
		log.info(Saas.legal_person_idcard_asset_overdue_count_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务中当前有效授信额度总和")
	def legal_person_idcard_credit_amount_sum_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务中当前有效授信额度总和"""
		sql = f"""
		select c.user_id from credit c, credit_entity ce
		where c.id = ce.credit_id
		and ce.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
		select ifnull(sum(total_amount), 0)as c from amount 
		where user_id in {str(user_list).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.legal_person_idcard_credit_amount_sum_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务中当前有效授信次数")
	def legal_person_idcard_count_credit_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务中当前有效授信次数"""
		sql = f"""
				select c.user_id from credit c, credit_entity ce
				where c.id = ce.credit_id
				and ce.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
				"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
		select ifnull(count(*), 0)as c from amount where 
		user_id in {str(user_list).replace("[", "(").replace("]", ")")}
		and expire = 0;
		"""
		log.info(Saas.legal_person_idcard_count_credit_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务中当前在贷本金")
	def legal_person_idcard_asset_inloan_principal_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id 
		AND p.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
		"""
		log.info(Saas.legal_person_idcard_asset_inloan_principal_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务的历史最大逾期天数")
	def legal_person_idcard_asset_max_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务的历史最大逾期天数"""
		sql = f"""
		select ifnull(max(a.largest_overdue_days), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id 
		AND p.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
		"""
		log.info(Saas.legal_person_idcard_asset_max_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务的当前最大逾期天数")
	def legal_person_idcard_asset_max_overdue_days_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务的当前最大逾期天数"""
		sql = f"""
		select ifnull(max(a.cur_overdue_days), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id 
		AND p.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
		"""
		log.info(Saas.legal_person_idcard_asset_max_overdue_days_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷业务的当前逾期在贷本金")
	def legal_person_idcard_asset_overdue_inloan_principal_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷业务的当前逾期在贷本金"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id and a.cur_overdue_days > 0
		AND p.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
		"""
		log.info(Saas.legal_person_idcard_asset_overdue_inloan_principal_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人手机号查询，在小贷业务中当前逾期订单笔数")
	def legal_person_mobile_asset_overdue_count_cur_cloudloan(self, env: str, **kwargs):
		"""按法人手机号查询，在小贷业务中当前逾期订单笔数"""
		sql = f"""
		select ifnull(count(*), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id 
		and a.cur_overdue_days > 0
		AND p.legal_person_phone_encrypt = '{kwargs["legal_phone"]}';
		"""
		log.info(Saas.legal_person_mobile_asset_overdue_count_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人手机号查询，在小贷业务的历史最大逾期天数")
	def legal_person_mobile_asset_max_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按法人手机号查询，在小贷业务的历史最大逾期天数"""
		sql = f"""
		select ifnull(max(a.largest_overdue_days), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id
		AND p.legal_person_phone_encrypt = '{kwargs["legal_phone"]}';
		"""
		log.info(Saas.legal_person_mobile_asset_max_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人手机号查询，在小贷业务的当前最大逾期天数")
	def legal_person_mobile_asset_max_overdue_days_cur_cloudloan(self, env: str, **kwargs):
		"""按法人手机号查询，在小贷业务的当前最大逾期天数"""
		sql = f"""
		select ifnull(max(a.cur_overdue_days), 0)as c from asset a, project_entity_detail p
		where a.project_id = p.project_id and a.cur_overdue_days > 0
		AND p.legal_person_phone_encrypt = '{kwargs["legal_phone"]}';
		"""
		log.info(Saas.legal_person_mobile_asset_max_overdue_days_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按申请公司统一社会信用代码查询，在小贷业务中当前有效授信额度总和")
	def work_company_id_credit_amount_sum_cur_cloudloan(self, env: str, **kwargs):
		"""按申请公司统一社会信用代码查询，在小贷业务中当前有效授信额度总和"""
		sql = f"""
		select c.user_id as user_id from credit c, credit_entity ce
		where c.id = ce.credit_id 
		and ce.unified_social_credit_code = '{kwargs["work_company_id"]}'
		UNION
		select c.user_id as user_id from credit c, credit_entity ce
		where c.id = ce.credit_id 
		and CASE 
			WHEN JSON_VALID(ce.extra_info) 
			THEN json_extract(ce.extra_info, '$.enterpriseCertificateNum') = '{kwargs["work_company_id"]}'
			ELSE NULL
		END
		UNION
		select c.user_id as user_id from credit c, credit_entity ce
		where c.id = ce.credit_id 
		and CASE 
			WHEN JSON_VALID(ce.extra_info) 
			THEN json_extract(ce.extra_info, '$.clinicUnifiedSocialCreditCode') = '{kwargs["work_company_id"]}'
			ELSE NULL
		END
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql2 = f"""
		select ifnull(sum(total_amount), 0)as c from amount
		where expire=0
		and user_id in {str(user_list).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.work_company_id_credit_amount_sum_cur_cloudloan.__doc__)
		res = self.exec_select(sql2, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按申请公司统一社会信用代码查询，在小贷业务中当前逾期订单笔数")
	def work_company_id_asset_overdue_count_cur_cloudloan(self, env: str, **kwargs):
		"""按申请公司统一社会信用代码查询，在小贷业务中当前逾期订单笔数"""
		sql = f"""
		SELECT ifnull(count(1), 0) AS c
		FROM asset
		WHERE id IN (
			SELECT a.id
			FROM asset a, project_entity_detail p
			WHERE a.project_id = p.project_id
				AND a.cur_overdue_days > 0
				AND p.unified_social_credit_code = '{kwargs["work_company_id"]}'
			UNION
			SELECT a.id
			FROM asset a, project_entity_detail p
			WHERE a.project_id = p.project_id
				AND a.cur_overdue_days > 0
				AND CASE 
					WHEN JSON_VALID(p.extra_info) 
					THEN json_extract(p.extra_info, '$.enterpriseCertificateNum') = '{kwargs["work_company_id"]}'
					ELSE NULL
				END
			UNION
			SELECT a.id
			FROM asset a, project_entity_detail p
			WHERE a.project_id = p.project_id
				AND a.cur_overdue_days > 0
				AND CASE 
					WHEN JSON_VALID(p.extra_info) 
					THEN json_extract(p.extra_info, '$.clinicUnifiedSocialCreditCode') = '{kwargs["work_company_id"]}'
					ELSE NULL
				END
		);
		"""
		log.info(Saas.work_company_id_asset_overdue_count_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中其他产品（即不包含齿科商户贷）中当前在贷本金")
	def idcard_asset_inloan_principal_exclude_ckshd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中其他产品（即不包含齿科商户贷）中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id
		and a.product_code <>'FQ_JK_CKSHD';
		"""
		log.info(Saas.idcard_asset_inloan_principal_exclude_ckshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务商户贷产品（齿科商户贷和宠物商户贷）中当前在贷本金")
	def idcard_asset_inloan_principal_ckshd_cwshd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务商户贷产品（齿科商户贷和宠物商户贷）中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id
		and a.product_code in ('FQ_JK_CKSHD', 'FQ_JK_CWSHD');
		"""
		log.info(Saas.idcard_asset_inloan_principal_ckshd_cwshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷齿科商户贷产品组的授信有效总额度加总")
	def idcard_credit_amount_ckshd_cur_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷齿科商户贷产品组的授信有效总额度加总"""
		sql = f"""
			select c.user_id from credit c, credit_customer cc
			where c.id = cc.credit_id 
			and cc.id_card_num_encrypt = '{kwargs["id"]}';
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
		select ifnull(sum(total_amount), 0)as c from amount 
		where expire = 0 
		and product_group_id=1121
		and user_id in {str(user_list).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.idcard_credit_amount_ckshd_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷不包含牙医贷产品组的其他产品的在贷本金总和")
	def idcard_asset_inloan_principal_exclude_yyd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷不包含牙医贷产品组的其他产品的在贷本金总和"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset
		where project_id in (
		select pd.id from project_detail pd, project_customer_detail pcd 
		where pcd.card_num_encrypt = '{kwargs["id"]}'
		and pd.product_code not in ('XJ_JFX_YYDSIN','XJ_JFX_YYDMUL','XJ_JFX_YYDLAG')
		and pd.id = pcd.project_id);
		"""
		log.info(Saas.idcard_asset_inloan_principal_exclude_yyd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷齿科商户贷产品组的授信有效的总额度加总")
	def legal_person_id_credit_amount_ckshd_cur_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷齿科商户贷产品组的授信有效的总额度加总"""
		sql = f"""
		select c.user_id from credit c, credit_entity ce
		where c.id = ce.credit_id 
		and ce.legal_person_idcard_encrypt = '{kwargs["legal_id"]}';
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
		select ifnull(sum(total_amount), 0)as c from amount 
		where product_group_id = 1121 and expire = 0 and user_id in 
		{str(user_list).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.legal_person_id_credit_amount_ckshd_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按法人身份证号查询，在小贷齿科商户贷产品组在贷本金总和")
	def legal_person_id_asset_inloan_principal_ckshd_cloudloan(self, env: str, **kwargs):
		"""按法人身份证号查询，在小贷齿科商户贷产品组在贷本金总和"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset where project_id in (
		select p.id from project_detail p, project_entity_detail pe
		where p.id = pe.project_id and p.product_code in ('FQ_JK_CKSHD')
		AND pe.legal_person_idcard_encrypt = '{kwargs["legal_id"]}');
		"""
		log.info(Saas.legal_person_id_asset_inloan_principal_ckshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计牙医贷产品组在贷本金总和")
	def work_company_id_asset_inloan_principal_yyd_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计牙医贷产品组在贷本金总和"""
		sql = f"""
		SELECT ifnull(sum(debt_amount), 0) AS c
			FROM asset
			WHERE project_id IN (
				SELECT p.id
				FROM project_detail p, project_entity_detail ped
				WHERE p.id = ped.project_id
					AND p.product_code IN ('XJ_JFX_YYDSIN', 'XJ_JFX_YYDMUL', 'XJ_JFX_YYDLAG')
					AND ped.unified_social_credit_code = '{kwargs["work_company_id"]}'
				UNION
				SELECT p.id
				FROM project_detail p, project_entity_detail ped
				WHERE p.id = ped.project_id
					AND p.product_code IN ('XJ_JFX_YYDSIN', 'XJ_JFX_YYDMUL', 'XJ_JFX_YYDLAG')
					AND CASE 
						WHEN JSON_VALID(ped.extra_info) THEN 
						json_extract(ped.extra_info, '$.enterpriseCertificateNum') = '{kwargs["work_company_id"]}'
						ELSE NULL
					END
			);
		"""
		log.info(Saas.work_company_id_asset_inloan_principal_yyd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，小贷齿科商户贷产品组的授信有效的总额度加总")
	def work_company_id_credit_amount_ckshd_cur_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，小贷齿科商户贷产品组的授信有效的总额度加总"""
		sql = f"""
			select c.user_id from credit c inner join credit_entity ce
			on c.id=ce.credit_id where 
			ce.unified_social_credit_code = '{kwargs["work_company_id"]}'
			and c.product_group_id = 1121;
		"""
		user_id = self.exec_select(sql, env, "saas")
		if user_id is None:
			return 0
		user_list = []
		for p in user_id:
			user_list.append(p["user_id"])
		sql = f"""
		select ifnull(sum(total_amount), 0)as c from amount 
		where 
		user_id in {str(user_list).replace("[", "(").replace("]", ")")}
		and expire = 0;
		"""
		log.info(Saas.work_company_id_credit_amount_ckshd_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "nebula")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷不包含宠物商户贷产品组的其他产品的在贷本金总和")
	def idcard_asset_inloan_principal_exclude_cwshd_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷不包含宠物商户贷产品组的其他产品的在贷本金总和"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset
		where project_id in (
		select pd.id from project_detail pd, project_customer_detail pcd 
		where pcd.card_num_encrypt = '{kwargs["id"]}'
		and pd.product_code !='FQ_JK_CWSHD'
		and pd.id = pcd.project_id);
		"""
		log.info(Saas.idcard_asset_inloan_principal_exclude_cwshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计宠物商户贷产品组在贷本金总和")
	def work_company_id_asset_inloan_principal_cwshd_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计宠物商户贷产品组在贷本金总和"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset where project_id in (
		select p.id from project_detail p inner join project_entity_detail ped
		on p.id=ped.project_id 
		where p.product_code='FQ_JK_CWSHD'
		AND ped.unified_social_credit_code = '{kwargs["work_company_id"]}'
		);
		"""
		log.info(Saas.work_company_id_asset_inloan_principal_cwshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计订车贷商户在贷本金总和")
	def work_company_id_asset_inloan_principal_dcd_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计订车贷商户在贷本金总和"""
		sql = f"""
		select ifnull(sum(debt_amount), 0) as c from asset a inner join (
		select p.id from project_detail p, project_entity_detail ped 
		where p.id = ped.project_id and p.product_code="FQ_ZQYC_DCD"
		AND ped.unified_social_credit_code = '{kwargs["work_company_id"]}') b 
		where a.project_id=b.id;
		"""
		log.info(Saas.work_company_id_asset_inloan_principal_dcd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计订车贷商户最大逾期天数")
	def work_company_id_asset_max_overdue_days_cur_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计订车贷商户最大逾期天数"""
		sql = f"""
		select ifnull(max(cur_overdue_days), 0)as c from asset where project_id IN (
		select p.id from project_detail p, project_entity_detail ped 
		where p.id = ped.project_id 
		and p.product_code="FQ_ZQYC_DCD"
		AND ped.unified_social_credit_code = '{kwargs["work_company_id"]}');
		"""
		log.info(Saas.work_company_id_asset_max_overdue_days_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计订车贷商户历史最大逾期天数")
	def work_company_id_asset_max_overdue_days_history_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计订车贷商户历史最大逾期天数"""
		sql = f"""
		select ifnull(max(largest_overdue_days), 0)as c from asset where project_id IN (
		select p.id from project_detail p, project_entity_detail ped 
		where p.id = ped.project_id 
		and p.product_code="FQ_ZQYC_DCD"
		AND ped.unified_social_credit_code = '{kwargs["work_company_id"]}');
		"""
		log.info(Saas.work_company_id_asset_max_overdue_days_history_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中其他产品中（即不包含产品美牙分期）的当前在贷本金")
	def idcard_asset_inloan_principal_cur_exclude_fq_jfx_myfq_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中其他产品中（即不包含产品美牙分期）的当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a, project_customer_detail p
		where p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id 
		and a.product_code!="FQ_JFX_MYFQ";
		"""
		log.info(Saas.idcard_asset_inloan_principal_cur_exclude_fq_jfx_myfq_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务美牙分期产品中当前在贷本金")
	def idcard_asset_inloan_principal_fq_jfx_myfq_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务美牙分期产品中当前在贷本金"""
		sql = f"""
		select ifnull(sum(a.debt_amount), 0)as c from asset a inner join
		project_customer_detail p
		on a.project_id=p.project_id
		where p.card_num_encrypt = '{kwargs["id"]}'
		and a.project_id = p.project_id 
		and a.product_code="FQ_JFX_MYFQ";
		"""
		log.info(Saas.idcard_asset_inloan_principal_fq_jfx_myfq_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按场景方公司统一社会信用代码查询，在小贷业务中当前逾期订单笔数")
	def scene_holder_id_asset_overdue_count_cur_cloudloan(self, env: str, **kwargs):
		"""按场景方公司统一社会信用代码查询，在小贷业务中当前逾期订单笔数"""
		sql = f"""
		select ifnull(count(1), 0)as c from asset where
		cur_overdue_days>0
		and project_id in(
		select project_id from project_entity_detail where
		unified_social_credit_code ='{kwargs["scene_holder_id"]}'
		);
		"""
		log.info(Saas.scene_holder_id_asset_overdue_count_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按场景方公司统一社会信用代码查询，在小贷业务中当前最大逾期天数")
	def scene_holder_id_asset_max_overdue_days_cur_cloudloan(self, env: str, **kwargs):
		"""按场景方公司统一社会信用代码查询，在小贷业务中当前最大逾期天数"""
		sql = f"""
		select ifnull(max(cur_overdue_days), 0)as c from asset a 
		inner join 
		project_entity_detail ped
		on a.project_id=ped.project_id where
		ped.unified_social_credit_code ='{kwargs["scene_holder_id"]}';
		"""
		log.info(Saas.scene_holder_id_asset_max_overdue_days_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按场景方公司统一社会信用代码查询，在小贷金服侠美牙分期总在贷本金")
	def scene_holder_id_asset_inloan_principal_fq_jfx_myfq_cloudloan(self, env: str, **kwargs):
		"""按场景方公司统一社会信用代码查询，在小贷金服侠美牙分期总在贷本金"""
		sql = f"""
		select project_id from project_entity_detail where
		unified_social_credit_code ='{kwargs["scene_holder_id"]}';
		"""
		project_id = self.exec_select(sql, env, "saas")
		if project_id is None:
			return 0
		project_id_list = []
		for p in project_id:
			project_id_list.append(p["project_id"])
		sql = f"""
				select ifnull(sum(debt_amount), 0)as c from ds_fund_asset
				where 
				project_id in {str(project_id_list).replace("[", "(").replace("]", ")")};
				"""
		log.info(Saas.scene_holder_id_asset_inloan_principal_fq_jfx_myfq_cloudloan.__doc__)
		res = self.exec_select(sql, env, "data_station")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计小贷所有业务在贷本金总和")
	def work_company_id_asset_inloan_principal_cur_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计小贷所有业务在贷本金总和"""
		sql = f"""
				select ifnull(sum(a.debt_amount), 0)as c from asset a
				inner join  (
				select p.id from project_detail p inner join project_entity_detail ped 
				on p.id = ped.project_id 
				where ped.unified_social_credit_code = '{kwargs["work_company_id"]}') b
				on a.project_id=b.id;
				"""
		log.info(Saas.work_company_id_asset_inloan_principal_cur_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按社会统一信用代码查询，统计宠物商户贷产品组在贷本金总和")
	def work_company_id_asset_inloan_principal_cwshd_cloudloan(self, env: str, **kwargs):
		"""按社会统一信用代码查询，统计宠物商户贷产品组在贷本金总和"""
		sql = f"""
		select ifnull(sum(debt_amount), 0)as c from asset where project_id IN (
		select p.id from project_detail p, project_entity_detail ped 
		where p.id = ped.project_id and p.product_code in ('FQ_JK_CWSHD')
		AND ped.unified_social_credit_code = '{kwargs["work_company_id"]}');
		"""
		log.info(Saas.work_company_id_asset_inloan_principal_cwshd_cloudloan.__doc__)
		res = self.exec_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务云易花产品组中当前在贷本金")
	def idcard_asset_inloan_principal_fq_he_yyh_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务云易花产品组中当前在贷本金"""
		sql = f"""
		select infull(sum(debt_amount), 0) as c from asset a, project_customer_detail pcd  
		where a.product_code in ('FQ_HE_YYH', 'FQ_HE_KAYYH')
		and a.project_id = pcd.project_id and pcd.card_num_encrypt = '{kwargs['id']}');
		"""
		log.info(Saas.idcard_asset_inloan_principal_fq_he_yyh_cloudloan.__doc__)
		res = self.excel_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按身份证号查询，在小贷业务中其他产品（即不包含云易花产品组）中当前在贷本金")
	def idcard_asset_inloan_principal_exclude_fq_he_yyh_cloudloan(self, env: str, **kwargs):
		"""按身份证号查询，在小贷业务中其他产品（即不包含云易花产品组）中当前在贷本金"""
		sql = f"""
		select infull(sum(debt_amount), 0) as c from asset a, project_customer_detail pcd  
		where a.product_code not in ('FQ_HE_YYH', 'FQ_HE_KAYYH')
		and a.project_id = pcd.project_id and pcd.card_num_encrypt = '{kwargs['id']}');
		"""
		log.info(Saas.idcard_asset_inloan_principal_exclude_fq_he_yyh_cloudloan.__doc__)
		res = self.excel_select(sql, env, "saas")[0].get("c", 0)
		return res

	@excep
	@allure.step("按场景方公司统一社会信用代码查询，在小贷业务云易花产品组中，所有借款人在该场景方当前在贷本金")
	def scene_holder_id_asset_inloan_principal_fq_he_yyh_cloudloan(self, env: str, **kwargs):
		"""按场景方公司统一社会信用代码查询，在小贷业务云易花产品组中，所有借款人在该场景方当前在贷本金"""
		sql = f"""
			select pd.id from project_detail pd, project_entity_detail ped 
			where ped.unified_social_credit_code = '{kwargs["scene_holder_id"]}'
			and and pd.product_code in ('FQ_HE_YYH','FQ_HE_KAYYH');
			"""
		project_id = self.exec_select(sql, env, "saas")
		if project_id is None:
			return 0
		project_id_list = []
		for p in project_id:
			project_id_list.append(p["project_id"])
		sql = f"""
		select ifnull(sum(debt_amount), 0) as c from ds_fund_asset
		where project_id in {str(project_id_list).replace("[", "(").replace("]", ")")};
		"""
		log.info(Saas.scene_holder_id_asset_inloan_principal_fq_jfx_myfq_cloudloan.__doc__)
		res = self.exec_select(sql, env, "data_station")[0].get("c", 0)
		return res
